import arrow
import pandas as pd
import os
from openpyxl import load_workbook

# 获取所有表格文件的路径

# 时间
now = arrow.now()
nowdate = now.shift(months=-1).format('YYYY-MM')

folder_path = 'D:/税务机器人/汇总/' + nowdate  # 写个时间
file_names = os.listdir(folder_path)
file_paths = [os.path.join(folder_path, file_name) for file_name in file_names if file_name.endswith('.xlsx')]

# 读取第一个表格文件来初始化结果DataFrame
# result_df = pd.read_excel(file_paths[0], sheet_name='附表8')

# 将非数值型数据转换为NaN
# result_df = result_df.apply(pd.to_numeric, errors='coerce')

# 逐个读取表格文件并进行求和操作
# for file_path in file_paths[1:]:
#     df = pd.read_excel(file_path, sheet_name='附表8')  # 读取表格文件
#     df = df.apply(pd.to_numeric, errors='coerce')  # 将非数值型数据转换为NaN
#     result_df = result_df.add(df, fill_value=0)  # 求和操作，缺失值用0填充
#
# # 将结果写入新表格文件
# result_file_path = 'D:\税务机器人\2023-06\huizong.xlsx'
# # with pd.ExcelWriter(result_file_path, engine='openpyxl') as writer:
# with pd.ExcelWriter(result_file_path, engine='openpyxl', mode='a', if_sheet_exists='replace') as writer:
#     book = load_workbook(result_file_path)
#     writer.book = book      # 读取excel
#     writer.sheets = dict((ws.title, ws) for ws in book.worksheets)          # 复制excel的所有表
#     result_df.to_excel(writer, sheet_name='附件8', index=None)


# # 附表1
# result_df = pd.read_excel(file_paths[0], sheet_name='附表1')
#
# # 将非数值型数据转换为NaN
# result_df = result_df.apply(pd.to_numeric, errors='coerce')
#
# # 逐个读取表格文件并进行求和操作
# for file_path in file_paths[1:]:
#     df = pd.read_excel(file_path, sheet_name='附表1')  # 读取表格文件
#     df = df.apply(pd.to_numeric, errors='coerce')  # 将非数值型数据转换为NaN
#     result_df = result_df.add(df, fill_value=0)  # 求和操作，缺失值用0填充
#
# # 将结果写入新表格文件
# result_file_path = 'D:\税务机器人\2023-06\huizong.xlsx'
# # with pd.ExcelWriter(result_file_path, engine='openpyxl') as writer:
# with pd.ExcelWriter(result_file_path, engine='openpyxl', mode='a', if_sheet_exists='replace') as writer:
#     book = load_workbook(result_file_path)
#     writer.book = book      # 读取excel
#     writer.sheets = dict((ws.title, ws) for ws in book.worksheets)          # 复制excel的所有表
#     result_df.to_excel(writer, sheet_name='附件1', index=None)
#
#
# # 附表2
# result_df = pd.read_excel(file_paths[0], sheet_name='附表2')
#
# # 将非数值型数据转换为NaN
# result_df = result_df.apply(pd.to_numeric, errors='coerce')
#
# # 逐个读取表格文件并进行求和操作
# for file_path in file_paths[1:]:
#     df = pd.read_excel(file_path, sheet_name='附表2')  # 读取表格文件
#     df = df.apply(pd.to_numeric, errors='coerce')  # 将非数值型数据转换为NaN
#     result_df = result_df.add(df, fill_value=0)  # 求和操作，缺失值用0填充
#
# # 将结果写入新表格文件
# result_file_path = 'D:\税务机器人\2023-06\huizong.xlsx'
# # with pd.ExcelWriter(result_file_path, engine='openpyxl') as writer:
# with pd.ExcelWriter(result_file_path, engine='openpyxl', mode='a', if_sheet_exists='replace') as writer:
#     book = load_workbook(result_file_path)
#     writer.book = book      # 读取excel
#     writer.sheets = dict((ws.title, ws) for ws in book.worksheets)          # 复制excel的所有表
#     result_df.to_excel(writer, sheet_name='附件2', index=None)
#
#
# # 附表3
# result_df = pd.read_excel(file_paths[0], sheet_name='附表3')
#
# # 将非数值型数据转换为NaN
# result_df = result_df.apply(pd.to_numeric, errors='coerce')
#
# # 逐个读取表格文件并进行求和操作
# for file_path in file_paths[1:]:
#     df = pd.read_excel(file_path, sheet_name='附表3')  # 读取表格文件
#     df = df.apply(pd.to_numeric, errors='coerce')  # 将非数值型数据转换为NaN
#     result_df = result_df.add(df, fill_value=0)  # 求和操作，缺失值用0填充
#
# # 将结果写入新表格文件
# result_file_path = 'D:\税务机器人\2023-06\huizong.xlsx'
# # with pd.ExcelWriter(result_file_path, engine='openpyxl') as writer:
# with pd.ExcelWriter(result_file_path, engine='openpyxl', mode='a', if_sheet_exists='replace') as writer:
#     book = load_workbook(result_file_path)
#     writer.book = book      # 读取excel
#     writer.sheets = dict((ws.title, ws) for ws in book.worksheets)          # 复制excel的所有表
#     result_df.to_excel(writer, sheet_name='附件3', index=None)
#
#
# # 附表4
# result_df = pd.read_excel(file_paths[0], sheet_name='附表4')
#
# # 将非数值型数据转换为NaN
# result_df = result_df.apply(pd.to_numeric, errors='coerce')
#
# # 逐个读取表格文件并进行求和操作
# for file_path in file_paths[1:]:
#     df = pd.read_excel(file_path, sheet_name='附表4')  # 读取表格文件
#     df = df.apply(pd.to_numeric, errors='coerce')  # 将非数值型数据转换为NaN
#     result_df = result_df.add(df, fill_value=0)  # 求和操作，缺失值用0填充
#
# # 将结果写入新表格文件
# result_file_path = 'D:\税务机器人\2023-06\huizong.xlsx'
# # with pd.ExcelWriter(result_file_path, engine='openpyxl') as writer:
# with pd.ExcelWriter(result_file_path, engine='openpyxl', mode='a', if_sheet_exists='replace') as writer:
#     book = load_workbook(result_file_path)
#     writer.book = book      # 读取excel
#     writer.sheets = dict((ws.title, ws) for ws in book.worksheets)          # 复制excel的所有表
#     result_df.to_excel(writer, sheet_name='附件4', index=None)
#
#
# # 附表5
# result_df = pd.read_excel(file_paths[0], sheet_name='附表5')
#
# # 将非数值型数据转换为NaN
# result_df = result_df.apply(pd.to_numeric, errors='coerce')
#
# # 逐个读取表格文件并进行求和操作
# for file_path in file_paths[1:]:
#     df = pd.read_excel(file_path, sheet_name='附表5')  # 读取表格文件
#     df = df.apply(pd.to_numeric, errors='coerce')  # 将非数值型数据转换为NaN
#     result_df = result_df.add(df, fill_value=0)  # 求和操作，缺失值用0填充
#
# # 将结果写入新表格文件
# result_file_path = 'D:\税务机器人\2023-06\huizong.xlsx'
# # with pd.ExcelWriter(result_file_path, engine='openpyxl') as writer:
# with pd.ExcelWriter(result_file_path, engine='openpyxl', mode='a', if_sheet_exists='replace') as writer:
#     book = load_workbook(result_file_path)
#     writer.book = book      # 读取excel
#     writer.sheets = dict((ws.title, ws) for ws in book.worksheets)          # 复制excel的所有表
#     result_df.to_excel(writer, sheet_name='附件5', index=None)
#

# 附表6
# result_df = pd.read_excel(file_paths[0], sheet_name='附表6')
#
# # 将非数值型数据转换为NaN
# result_df = result_df.apply(pd.to_numeric, errors='coerce')
#
# # 逐个读取表格文件并进行求和操作
# for file_path in file_paths[1:]:
#     df = pd.read_excel(file_path, sheet_name='附表6')  # 读取表格文件
#     df = df.apply(pd.to_numeric, errors='coerce')  # 将非数值型数据转换为NaN
#     result_df = result_df.add(df, fill_value=0)  # 求和操作，缺失值用0填充
#
# # 将结果写入新表格文件
# result_file_path = 'D:\税务机器人\2023-06\huizong.xlsx'
# # with pd.ExcelWriter(result_file_path, engine='openpyxl') as writer:
# with pd.ExcelWriter(result_file_path, engine='openpyxl', mode='a', if_sheet_exists='replace') as writer:
#     book = load_workbook(result_file_path)
#     writer.book = book      # 读取excel
#     writer.sheets = dict((ws.title, ws) for ws in book.worksheets)          # 复制excel的所有表
#     result_df.to_excel(writer, sheet_name='附件6', index=None)
#
#
# # 附表7
# result_df = pd.read_excel(file_paths[0], sheet_name='附表7')
#
# # 将非数值型数据转换为NaN
# result_df = result_df.apply(pd.to_numeric, errors='coerce')
#
# # 逐个读取表格文件并进行求和操作
# for file_path in file_paths[1:]:
#     df = pd.read_excel(file_path, sheet_name='附表7')  # 读取表格文件
#     df = df.apply(pd.to_numeric, errors='coerce')  # 将非数值型数据转换为NaN
#     result_df = result_df.add(df, fill_value=0)  # 求和操作，缺失值用0填充
#
# # 将结果写入新表格文件
# result_file_path = 'D:\税务机器人\2023-06\huizong.xlsx'
# # with pd.ExcelWriter(result_file_path, engine='openpyxl') as writer:
# with pd.ExcelWriter(result_file_path, engine='openpyxl', mode='a', if_sheet_exists='replace') as writer:
#     book = load_workbook(result_file_path)
#     writer.book = book      # 读取excel
#     writer.sheets = dict((ws.title, ws) for ws in book.worksheets)          # 复制excel的所有表
#     result_df.to_excel(writer, sheet_name='附件7', index=None)

# 附表6
open_insert = 'D:\税务机器人\基础设置\汇总.xlsx'
result_file_path = f'D:\税务机器人\\{nowdate}\huizong.xlsx'
sheet_name = '附表6'
row_data = []
for file_path in file_paths:
    df = pd.read_excel(file_path, sheet_name=sheet_name, skiprows=3)
    df = pd.read_excel(file_path, sheet_name=sheet_name, skiprows=3,
                       usecols=[i for i in range(1, len(df.columns)) if i != 0])
    rows = df.iloc[0]
    row_data.append(rows.tolist())

# 保存文件
df_li = pd.DataFrame(row_data)
workbook = load_workbook(open_insert)

worksheet = workbook[sheet_name]
book = load_workbook(open_insert)
writer = pd.ExcelWriter(result_file_path, engine='openpyxl')

writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
df_li.to_excel(writer, sheet_name=sheet_name, index=False, header=False, startrow=4, startcol=1)
writer.save()
writer.close()



# 附表7
open_insert = 'D:\税务机器人\基础设置\汇总.xlsx'
result_file_path = f'D:\税务机器人\\{nowdate}\huizong.xlsx'
# 判断文件是否初始化
dir_name, file_name = os.path.split(result_file_path)
if file_name in os.listdir(dir_name):
    open_insert = result_file_path

sheet_name = '附表7'
row_data = []
for file_path in file_paths:
    df = pd.read_excel(file_path, sheet_name=sheet_name, skiprows=3)
    df = pd.read_excel(file_path, sheet_name=sheet_name, skiprows=3,
                       usecols=[i for i in range(1, len(df.columns)) if i != 0])
    rows = df.iloc[0]
    row_data.append(rows.tolist())

# 保存文件
df_li = pd.DataFrame(row_data)
workbook = load_workbook(open_insert)

worksheet = workbook[sheet_name]
book = load_workbook(open_insert)
writer = pd.ExcelWriter(result_file_path, engine='openpyxl')

writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
df_li.to_excel(writer, sheet_name=sheet_name, index=False, header=False, startrow=4, startcol=1)
writer.save()
writer.close()
